#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
验证Excel文件的结构
"""

import pandas as pd
import openpyxl
from openpyxl import load_workbook

def verify_excel_structure(filename: str):
    """验证Excel文件结构"""
    print(f"🔍 验证文件: {filename}")
    print("=" * 60)
    
    try:
        # 使用openpyxl加载文件
        wb = load_workbook(filename)
        ws = wb.active
        
        print(f"工作表名称: {ws.title}")
        print(f"最大行数: {ws.max_row}")
        print(f"最大列数: {ws.max_column}")
        
        # 显示前几行的内容
        print("\n📊 前5行内容:")
        print("-" * 60)
        
        for row in range(1, min(6, ws.max_row + 1)):
            row_data = []
            for col in range(1, ws.max_column + 1):
                cell_value = ws.cell(row=row, column=col).value
                if cell_value is None:
                    cell_value = ""
                row_data.append(str(cell_value))
            
            print(f"行 {row}: {' | '.join(row_data)}")
        
        # 检查合并单元格
        print(f"\n🔗 合并单元格信息:")
        print("-" * 60)
        for merged_range in ws.merged_cells.ranges:
            print(f"合并范围: {merged_range}")
        
        # 检查列宽
        print(f"\n📏 列宽信息:")
        print("-" * 60)
        for col in range(1, ws.max_column + 1):
            col_letter = openpyxl.utils.get_column_letter(col)
            width = ws.column_dimensions[col_letter].width
            print(f"列 {col_letter}: {width}")
        
        wb.close()
        
    except Exception as e:
        print(f"❌ 验证失败: {e}")

def verify_pandas_read(filename: str):
    """使用pandas验证Excel文件"""
    print(f"\n🐼 使用pandas读取: {filename}")
    print("=" * 60)
    
    try:
        # 读取Excel文件
        df = pd.read_excel(filename, header=None)
        
        print(f"数据形状: {df.shape}")
        print(f"列数: {len(df.columns)}")
        print(f"行数: {len(df)}")
        
        print("\n📊 前5行数据:")
        print("-" * 60)
        print(df.head())
        
        print(f"\n📊 数据类型:")
        print("-" * 60)
        print(df.dtypes)
        
    except Exception as e:
        print(f"❌ pandas读取失败: {e}")

def main():
    """主函数"""
    print("🚀 开始验证Excel文件结构...")
    print("=" * 60)
    
    # 验证两个文件
    files = [
        "japanpost_ems_exact_format.xlsx",
        "japanpost_ems_alternative.xlsx"
    ]
    
    for filename in files:
        verify_excel_structure(filename)
        verify_pandas_read(filename)
        print("\n" + "="*80 + "\n")

if __name__ == "__main__":
    main()
